
CREATE TABLE [dbo].[SM_TableClass](
	[Tab_Class] [int] NOT NULL,
	[Tab_ClassName] [varchar](20) NULL,
	[Tab_Classdes] [varchar](800) NULL,
 CONSTRAINT [PK_SM_TableClass] PRIMARY KEY CLUSTERED 
(
	[Tab_Class] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

insert SM_TableClass (Tab_Class,Tab_ClassName,Tab_Classdes) values( 0,'必备数据','不能清除此类数据，如果被删除，则系统无法正常运作.')
insert SM_TableClass (Tab_Class,Tab_ClassName,Tab_Classdes) values( 1,'基本数据','此类数据是建立业务数据的基础，所以清除此类数据同时也会清楚所有的业务数据！')
insert SM_TableClass (Tab_Class,Tab_ClassName,Tab_Classdes) values( 2,'业务数据','此类数据包括业务数据，出纳数据，卡片数据和财务数据等。重置后所有业务相关的数据都会清除，您可以重新开始业务流程！')
go

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'表类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'SM_TableClass'
GO


EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'SM_TableClass', @level2type = N'COLUMN',@level2name = N'Tab_Class'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类型名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'SM_TableClass', @level2type = N'COLUMN',@level2name = N'Tab_ClassName'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'SM_TableClass', @level2type = N'COLUMN',@level2name = N'Tab_Classdes'
GO

--exec sys.sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'SM_TypeClass', @level2type = N'COLUMN',@level2name = N'Tab_des'

CREATE TABLE [dbo].[SM_TableType](
	[Tab_name] [varchar](128) NOT NULL,
	[Tab_des] [varchar](50) NULL,
	[Tab_Class] [int] NULL,
 CONSTRAINT [PK_SM_TableType] PRIMARY KEY CLUSTERED 
(
	[Tab_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SM_TableType]  WITH CHECK ADD  CONSTRAINT [FK_SM_TableType_SM_TableClass] FOREIGN KEY([Tab_Class])
REFERENCES [dbo].[SM_TableClass] ([Tab_Class])
GO

ALTER TABLE [dbo].[SM_TableType] CHECK CONSTRAINT [FK_SM_TableType_SM_TableClass]
GO
--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'表说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'SM_TableType'
GO

EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'表名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'SM_TableType', @level2type = N'COLUMN',@level2name = N'Tab_name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'表说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'SM_TableType', @level2type = N'COLUMN',@level2name = N'Tab_des'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'表类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'SM_TableType', @level2type = N'COLUMN',@level2name = N'Tab_Class'
GO


CREATE TABLE [bs_customer]  (
  [cus_id] int  NOT NULL identity(1,1) ,
  [cus_code] varchar(20) NOT NULL DEFAULT '',
  [cus_name] varchar(30) NOT NULL DEFAULT '',
  [cus_fullname] varchar(100) NOT NULL DEFAULT '' ,
  [cus_email] varchar(100) NOT NULL DEFAULT '' ,
  [cus_address] varchar(200) NOT NULL DEFAULT '' ,
  [cus_tel] varchar(50) NOT NULL DEFAULT '' ,
  [cus_fax] varchar(50) NOT NULL DEFAULT '' ,
  [cus_taxcode] varchar(30) NOT NULL DEFAULT '' ,  
  [cus_ifstop] bit NOT NULL DEFAULT 0 ,
  [cus_createby] varchar(50) NOT NULL DEFAULT '' ,
  [cus_createtime] datetime NOT NULL DEFAULT GETDATE(),
  [cus_UpdatedTime] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  CONSTRAINT [PK_bs_customer] PRIMARY KEY CLUSTERED ([cus_id] ASC)
) 
GO
--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'bs_customer'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_id'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户编码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户全称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_fullname'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户电子邮件', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_email'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户地址', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_address'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户电话', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_tel'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户传真', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_fax'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户税号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_taxcode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否使用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_ifstop'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建人', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_createby'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_createtime'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'更新时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_UpdatedTime'
GO

-- ----------------------------
-- Table structure for bs_provider
-- ----------------------------

CREATE TABLE [bs_provider]  (  
  [Pro_id] int  NOT NULL IDENTITY(1,1),
  [Pro_code] varchar(20) NOT NULL ,
  [Pro_name] varchar(30) NOT NULL ,
  [Pro_fullname] varchar(100) NOT NULL DEFAULT '' ,
  [Pro_email] varchar(100) NOT NULL DEFAULT '' ,
  [Pro_address] varchar(200) NOT NULL DEFAULT '' ,
  [Pro_tel] varchar(50) NOT NULL DEFAULT '' ,
  [Pro_fax] varchar(50) NOT NULL DEFAULT '' ,
  [Pro_taxcode] varchar(30) NOT NULL DEFAULT '' ,  
  [pro_ifstop] bit NOT NULL DEFAULT 0  ,
  [pro_createby] varchar(50) NOT NULL DEFAULT '' ,
  [pro_createtime] datetime NOT NULL ,
  [pro_UpdatedTime] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  CONSTRAINT [PK_bs_provider] PRIMARY KEY CLUSTERED ([Pro_id] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'bs_provider'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_id'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商编码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商全称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_fullname'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商电子邮件', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_email'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商地址', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_address'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商电话', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_tel'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商传真', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_fax'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商税号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_taxcode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否使用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_ifstop'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建人', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_createby'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_createtime'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'更新时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_UpdatedTime'
GO



CREATE TABLE [dbo].[BS_MatType](
	[MTP_CODE] [varchar](20) NOT NULL,
	[MTP_Name] [varchar](50) NOT NULL,
	[MTP_ParCode] [varchar](20) NULL,
	[MTP_Property] [int] NOT NULL,
	[MTP_Remark] [varchar](200) NULL,
 CONSTRAINT [PK_BS_MatType] PRIMARY KEY CLUSTERED 
(
	[MTP_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BS_MatType] ADD  CONSTRAINT [DF_BS_MatType_MTP_TYPE]  DEFAULT ((0)) FOR [MTP_Property]
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'物料类别列', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'BS_MatType'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类别编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MatType', @level2type = N'COLUMN',@level2name = N'MTP_CODE'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类别', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MatType', @level2type = N'COLUMN',@level2name = N'MTP_Name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'父级类别编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MatType', @level2type = N'COLUMN',@level2name = N'MTP_ParCode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'属性', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MatType', @level2type = N'COLUMN',@level2name = N'MTP_Property'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MatType', @level2type = N'COLUMN',@level2name = N'MTP_Remark'
GO



CREATE TABLE [dbo].[BS_MATERIAL](
	[mat_code] varchar(20) NOT NULL DEFAULT '',
	[mat_name] varchar(200) NOT NULL DEFAULT '',
	[mat_spec] varchar(200) NOT NULL DEFAULT '',
	[unt_code] varchar(20) NOT NULL DEFAULT '',
	[mat_type] varchar(20) NOT NULL DEFAULT '',
	[mat_barcode] varchar(50) NOT NULL DEFAULT '',
	[mat_remark] varchar(200) NOT NULL DEFAULT '',
	[mat_py] varchar(100) NOT NULL DEFAULT '',
	[mat_ifstop] bit NOT NULL DEFAULT 0,
	[mtp_code] varchar(20) NOT NULL DEFAULT '',
	[mat_maxstock] numeric(18, 6) NOT NULL DEFAULT 0,
	[mat_minstock] numeric(18, 6) NOT NULL DEFAULT 0,
	[mat_from] varchar(1) NOT NULL DEFAULT 'M',
	[mat_property] int NOT NULL DEFAULT 0,		
	[inci_id] int NULL DEFAULT '',
 CONSTRAINT [PK_BS_MATERIAL] PRIMARY KEY CLUSTERED 
(
	[mat_code] ASC
)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字典表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'BS_MATERIAL'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'物料编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'规格', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_spec'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'单位', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'unt_code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_type'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'条码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_barcode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_remark'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'助记符', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_py'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'停用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_ifstop'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类别', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mtp_code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'最大库存', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_maxstock'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'最小库存', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_minstock'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'来源', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_from'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'属性', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_property'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'INCI', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'inci_id'
GO

create table sa_saleorder(
SOR_Code varchar(20) not null,
CUS_Code varchar(20) not null,
CUS_Name varchar(100) not null,
SOR_PO varchar(20) not null,
SOR_Clients	varchar(50) not null,
SOR_CPO	varchar(20) not null,
SOR_Date varchar(10) not null,
SOR_Status	varchar(20) not null,
SOR_Remark	varchar(200) not null,
SOR_CreatedBy varchar(20) not null,
SOR_CreatedTime	datetime not null,	
SOR_ApprovedBy	varchar(20) null,
SOR_ApprovedTime datetime,	
SOR_UpdatedTime	datetime,
 CONSTRAINT [PK_SA_SALEORDER] PRIMARY KEY CLUSTERED 
(
	[SOR_Code] ASC
)
)

EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'成品布销售订单', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sa_saleorder'
GO

EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'销售订单编码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户编码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'CUS_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'CUS_Name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户PO', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_PO'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客人', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_Clients'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客人PO', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_CPO'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'下单日期', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_Date'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'订单状态', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_Status'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'备注', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_Remark'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建人', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_CreatedBy'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_CreatedTime'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'审批人', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_ApprovedBy'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'审批时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_ApprovedTime'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'更新时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sa_saleorder', @level2type = N'COLUMN',@level2name = N'SOR_UpdatedTime'
GO







-- ----------------------------
-- Table structure for sm_dictionary
-- ----------------------------
CREATE TABLE [sm_dictionary]( 
  [DIC_ID] int  NOT NULL identity(1,1),
  [DIC_TableCode] varchar(200) NULL DEFAULT '' ,
  [DIC_Code] varchar(100) NOT NULL,
  [DIC_Name] varchar(100) NULL DEFAULT '' ,
  [Dic_DataKind] varchar(40) NULL DEFAULT '' ,
  [DIC_Display] bit NULL DEFAULT 1 ,
  [Dic_Width] int NOT NULL DEFAULT 0 ,
  [Dic_IndexNo] int  NOT NULL DEFAULT 0,
  [DIC_SelectValues] varchar(800) NULL DEFAULT '' ,
  [DIC_ifSQL] bit NOT NULL DEFAULT 0 ,
  [Dic_ifFindSql] bit NOT NULL DEFAULT 0 ,
  [Dic_FinSql] varchar(1600) NULL DEFAULT '' ,
  CONSTRAINT [PK_sm_dictionary] PRIMARY KEY CLUSTERED ([DIC_ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字典表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_dictionary'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字典ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'表名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_TableCode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_Name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'Dic_DataKind'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否显示', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_Display'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'宽度', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'Dic_Width'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'序号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'Dic_IndexNo'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'选择值', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_SelectValues'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否使用查询', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_ifSQL'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否使用查找', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'Dic_ifFindSql'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'查询语句', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'Dic_FinSql'
GO

-- ----------------------------
-- Table structure for sm_modulecotrol
-- ----------------------------
CREATE TABLE [sm_modulecotrol]  ( 
  [MDC_ID] int NOT NULL identity(1,1) ,
  [MOD_CODE] varchar(20) NOT NULL,
  [MDC_TableName] varchar(50) NOT NULL,
  [MDC_KeyField] varchar(50) NULL DEFAULT '' ,
  [MDC_IfAutoNumber] bit NULL DEFAULT '' ,
  [MDC_NumberHead] varchar(5) NULL DEFAULT '' ,
  [MDC_NumberRule] varchar(50) NULL DEFAULT '' ,
  [MDC_KeyNo] varchar(50) NULL DEFAULT '' ,
  [MDC_Year] tinyint NULL DEFAULT '' ,
  [MDC_Month] tinyint NULL DEFAULT '' ,
  [MDC_Day] tinyint NULL DEFAULT '' ,
  [MDC_Inc] tinyint NULL DEFAULT '' ,
  [mdc_createrfield] varchar(50) NULL DEFAULT '',
  [mdc_createtimefield] varchar(50) NULL DEFAULT '',
  [MDC_CheckField] varchar(50) NULL DEFAULT '',
  [mdc_checktimefield] varchar(50) NULL DEFAULT '',
  [MDC_ApproveField] varchar(50) NULL DEFAULT '',
  [mdc_approvetimefield] varchar(50) NULL DEFAULT '',
  [MDC_AuditField] varchar(50) NULL DEFAULT '',
  [MDC_AuditTimeField] varchar(50) NULL DEFAULT '',
  [MDC_CloseField] varchar(50) NULL DEFAULT '',
  [MDC_CloseTimeField] varchar(50) NULL DEFAULT '',
  [MDC_CancelField] varchar(50) NULL DEFAULT '',
  [MDC_CancelTimeField] varchar(50) NULL DEFAULT '',
  CONSTRAINT [PK_sm_modulecotrol] PRIMARY KEY CLUSTERED ([MDC_ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块控制表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modulecotrol'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块控制ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MOD_CODE'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'主表名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_TableName'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'主键字段名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_KeyField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否自动编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_IfAutoNumber'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'编号头', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_NumberHead'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'编号规则', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_NumberRule'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_KeyNo'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'年份位数', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_Year'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'月份位数', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_Month'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'日份位数', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_Day'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'流水号长度', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_Inc'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'mdc_createrfield'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'mdc_createtimefield'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'提交人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_CheckField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'提交时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'mdc_checktimefield'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'审核人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_ApproveField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'审核时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'mdc_approvetimefield'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'记账人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_AuditField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'记账时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_AuditTimeField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'完结人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_CloseField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'完结时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_CloseTimeField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'取消人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_CancelField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'取消时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_CancelTimeField'

GO

-- ----------------------------
-- Table structure for sm_modulefields
-- ----------------------------

CREATE TABLE [sm_modulefields]  ( 
  [MDF_ID] int NOT NULL identity(1,1) ,
  [MOD_Code] varchar(20) NOT NULL,
  [MDF_QueryName] varchar(50) NOT NULL,
  [MDF_Tablename] varchar(255) NOT NULL,
  [MDF_FieldName] varchar(50) NOT NULL,
  [MDF_Displaylabel] varchar(100) NOT NULL,
  [MDF_Display] bit NOT NULL DEFAULT 1 ,
  [MDF_Width] int NOT NULL DEFAULT 1,
  [MDF_ColWidth] int NOT NULL DEFAULT 10,
  [MDF_Index] int NOT NULL DEFAULT 0 ,
  [MDF_LookupSQL] varchar(1000) NOT NULL DEFAULT '',
  [MDF_LookupField] varchar(1000) NOT NULL DEFAULT '',
  [MDF_SelectSQL] varchar(1000) NOT NULL DEFAULT '',
  [MDF_SelectField] varchar(1000) NOT NULL DEFAULT '',
  CONSTRAINT [PK_sm_modulefields] PRIMARY KEY CLUSTERED ([MDF_ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块字段表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modulefields'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块字段ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MOD_CODE'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'查询类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_QueryName'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'表名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_Tablename'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_FieldName'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段中文名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_Displaylabel'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否显示', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_Display'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'显示宽度', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_Width'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'列表宽度', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_ColWidth'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'序号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_Index'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'关联语句', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_LookupSQL'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'关联字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_LookupField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'搜索名句', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_SelectSQL'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'搜索字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_SelectField'

GO

-- ----------------------------
-- Table structure for sm_modulemasterset
-- ----------------------------
CREATE TABLE [sm_modulemasterset]  (  
  [MMS_ID] int NOT NULL identity(1,1),
  [MOD_Code] varchar(50) NOT NULL DEFAULT '',
  [MMS_Fieldname] varchar(50) NOT NULL DEFAULT '',
  [MMS_Type] varchar(50) NOT NULL DEFAULT 'TEXT' ,
  [MMS_Index] int  NOT NULL DEFAULT 0  ,
  [MMS_CanInput] bit NOT NULL DEFAULT 1  ,
  CONSTRAINT [PK_sm_modulemasterset] PRIMARY KEY CLUSTERED ([MMS_ID] ASC)
) 
GO


--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块主表编辑字段表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modulemasterset'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MMS_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MOD_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MMS_Fieldname'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'显示类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MMS_Type'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段顺序', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MMS_Index'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否可输入', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MMS_CanInput'

GO
-- ----------------------------
-- Table structure for sm_modulequery
-- ----------------------------
CREATE TABLE [sm_modulequery]  ( 
  [MQY_ID] int  NOT NULL identity(1,1) ,
  [mod_code] varchar(50) NOT NULL default '',
  [mqy_type] varchar(20) NOT NULL default '',
  [mqy_sql] varchar(4000) NOT NULL default '',
  CONSTRAINT [PK_sm_modulequery] PRIMARY KEY CLUSTERED ([MQY_ID] ASC)
)
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块查询语句表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modulequery'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulequery', @level2type = N'COLUMN',@level2name = N'MQY_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulequery', @level2type = N'COLUMN',@level2name = N'MOD_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulequery', @level2type = N'COLUMN',@level2name = N'mqy_type'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'执行脚本', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulequery', @level2type = N'COLUMN',@level2name = N'mqy_sql'

GO
-- ----------------------------
-- Table structure for sm_modulereports
-- ----------------------------
CREATE TABLE [sm_modulereports]  ( 
  [MDR_ID] int NOT NULL identity(1,1),
  [MOD_Code] varchar(20) NOT NULL DEFAULT '',
  [MDR_CODE] varchar(20) NOT NULL DEFAULT '',
  [MDR_Name] varchar(50) NOT NULL DEFAULT '',
  [MDR_IfDefault] bit NOT NULL DEFAULT 0 ,
  [MDR_IfUsed] bit NOT NULL DEFAULT 0 ,
  [MDR_Remark] varchar(200) NULL DEFAULT ''  ,
  [MDR_Data] image NULL ,
  [MDR_Index] int NOT NULL DEFAULT 0 ,
  [MDR_CreateDate] datetime NOT NULL,
  [MDR_ModifyDate] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  CONSTRAINT [PK_sm_modulereports] PRIMARY KEY CLUSTERED ([MDR_ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'报表模板表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modulereports'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'报表ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MOD_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'报表编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_CODE'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_Name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否默认', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_IfDefault'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否使用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_IfUsed'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_Remark'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'报表模板数据', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_Data'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'序号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_Index'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_CreateDate'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'修改时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_ModifyDate'
GO


-- ----------------------------
-- Table structure for sm_modules
-- ----------------------------

CREATE TABLE [sm_modules]  ( 
  [mod_id] int  NOT NULL identity(1,1) ,
  [mod_code] varchar(20) NOT NULL DEFAULT '',
  [mod_name] varchar(50) NOT NULL DEFAULT '',
  [mod_parcode] varchar(255) NOT NULL DEFAULT '',
  [mod_index] int NOT NULL DEFAULT 0 ,
  [mod_ifstop] bit NOT NULL DEFAULT 0  ,
  [mod_classtype] int  NOT NULL DEFAULT 0 ,
  CONSTRAINT [PK_sm_modules] PRIMARY KEY CLUSTERED ([mod_id] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modules'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_id'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'MOD_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'父级模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_parcode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'序号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_index'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否停用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_ifstop'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类的类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_classtype'

GO

-- ----------------------------
-- Table structure for sm_userpower
-- ----------------------------

CREATE TABLE [sm_userpower]  (
  [id] int  NOT NULL identity(1,1)  ,
  [use_id] int NOT NULL,
  [mod_id] int NOT NULL ,
  CONSTRAINT [PK_sm_userpower] PRIMARY KEY CLUSTERED ([ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户权限表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_userpower'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_userpower', @level2type = N'COLUMN',@level2name = N'ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_userpower', @level2type = N'COLUMN',@level2name = N'use_id'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_userpower', @level2type = N'COLUMN',@level2name = N'mod_id'

GO

-- ----------------------------
-- Table structure for sm_users
-- ----------------------------
CREATE TABLE [sm_users]  ( 
  [Use_ID] int  NOT NULL identity(1,1),
  [Use_Code] varchar(20) NOT NULL DEFAULT '',
  [Use_Name] varchar(50) NOT NULL DEFAULT '',
  [Use_phone] varchar(20) NOT NULL DEFAULT '',
  [Use_Email] varchar(50) NOT NULL DEFAULT '',
  [Use_password] varbinary(50) NOT NULL ,
  [Use_IfStop] bit NOT NULL DEFAULT 0 ,
  [Use_ifmanage] bit NOT NULL DEFAULT 0 ,
  CONSTRAINT [PK_sm_users] PRIMARY KEY CLUSTERED ([Use_ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_users'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户编码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_Name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'电话', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_phone'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'电子邮件', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_Email'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'密码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_password'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否停用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_IfStop'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否为管理员', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_ifmanage'
GO


create view dbo.readdescription
as
SELECT A.name AS table_name,D.value AS table_description,
 B.name AS column_name,
 C.value AS column_description
 FROM sys.tables A
 INNER JOIN sys.columns B ON B.object_id = A.object_id
 LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
left join sys.extended_properties D on a.object_id = d.major_id AND d.minor_id = 0
 WHERE A.name = '表名'

go


create view [dbo].[V_extended_properties]
	as
	Select C.name as fieldname,PFD.value as columnname,o.name as tablename,PTB.value as tabledesc,PTB.minor_id
                FROM      sys.columns AS C INNER JOIN
                sys.objects AS O ON C.object_id = O.object_id AND O.type = 'U' AND O.is_ms_shipped = 0 INNER JOIN
                sys.types AS T ON C.user_type_id = T.user_type_id LEFT OUTER JOIN
                sys.default_constraints AS D ON C.object_id = D.parent_object_id AND C.column_id = D.parent_column_id AND C.default_object_id = D.object_id 
                LEFT OUTER JOIN sys.extended_properties AS PFD ON PFD.class = 1 AND C.object_id = PFD.major_id AND C.column_id = PFD.minor_id 
                LEFT OUTER JOIN sys.extended_properties AS PTB ON PTB.class = 1 AND PTB.minor_id = 0 AND O.object_id = PTB.major_id 
GO


-- ----------------------------
-- Procedure structure for usp_createmodulefield
-- ----------------------------

CREATE PROCEDURE [dbo].[usp_createmodulefield](@modcode varchar(20),@tablename varchar(20),@type varchar(20))
as
begin

insert into sm_modulefields(MOD_Code,MDF_QueryName,MDF_TableName,MDF_FieldName,MDF_Displaylabel,MDF_Display,MDF_Width,MDF_ColWidth,MDF_Index)
SELECT  @modcode,@type, @tablename,name, name,1, COALESCE(MAX_LENGTH,10) as MDF_Width,COALESCE(MAX_LENGTH,10) as MDF_ColWidth, column_id
FROM sys.COLUMNS 
where object_name(object_id)=@tablename 
and not exists(select 1 from sm_modulefields where sm_modulefields.MOD_Code=@modcode and MDF_QueryName= @type and name=MDF_FieldName );

UPDATE sm_modulefields
SET MDF_Display = 0
WHERE MDF_FieldName LIKE '%_id';

insert into sm_modulemasterset(MOD_Code,MMS_Fieldname,MMS_Type,MMS_Index,MMS_CanInput)
select @modcode,name,case system_type_id when  104 then 'CHECK' when 61 then 'DATE' else 'TEXT' end ,column_id,1 
from sys.columns where object_name(object_id)=@tablename 
and not exists(select 1 from sm_modulemasterset where MOD_Code=@modcode and MMS_Fieldname=name )
order by column_id


end

Go






/* 
 功能: 生成数据脚本 
Usp_CreateDatAScript 'BS_UsrModule' ,'',' WHERE Comp_Name=''WF007'' ' 

*/ 
CREATE  PROCEDURE [dbo].[Usp_CreateDataScript] 
 @TABLEName VARCHAR(128), --数据表名 
 @notfield VARCHAR(255)='',--不需要的列 
 @WHERE VARCHAR(255)=''--查询条件 
AS 
DECLARE @TabID INT, 
  @InsertHead VARCHAR(8000), 
  @Values VARCHAR(8000), 
  @exestr VARCHAR(8000), 
  @column VARCHAR(128), 
  @xtype VARCHAR(128), 
  @i INT 
 SELECT @insertHead='' 
 SELECT @TabID=(SELECT top 1 id FROM sysobjects WITH(NOLOCK) WHERE name=@TABLEname) 
 DECLARE CurColumns cursor for SELECT A.name,B.name FROM syscolumns A,systypes B WITH(NOLOCK) WHERE A.xtype=B.xusertype AND id=@TabID 
 Open CurColumns 
 Fetch CurColumns INTO @column,@xtype 
 SELECT @i=1 
 while @@Fetch_status = 0 
  BEGIN 
 
  IF NOT (PATIndex('%'+@column +'%',@NotField+',')>0) 
  BEGIN 
   IF @insertHead=''  
   BEGIN 
    SELECT @insertHead=@column 
    IF @xtype IN ('money','float','int','smallint','decimal','numeric','real') 
    SELECT @Values='(CASE ISNULL(convert(VARCHAR(8000),'+@column+'),%d) WHEN %d THEN %s ELSE '+'convert(VARCHAR(8000),'+@column+') END)' 
    ELSE  
    SELECT @Values='(CASE ISNULL(convert(VARCHAR(8000),'+@column+',3),%d) WHEN %d THEN %s ELSE '+''''''''''+'+REPLACE(convert(VARCHAR(8000),'+@column+'),'''''''','''''''''''')+'+''''''''''+' END)' 
 
   END 
   ELSE  
   BEGIN 
    SELECT @insertHead=@insertHead+','+@column 
    IF @xtype IN ('money','float','int','smallint','decimal','numeric','real') 
    SELECT @Values=@Values+'+'+''''+','+''''+'+'+'(CASE ISNULL(convert(VARCHAR(8000),'+@column+'),%d) WHEN %d THEN %s ELSE '+'convert(VARCHAR(8000),'+@column+') END)' 
    ELSE  
    SELECT @Values=@Values+'+'+''''+','+''''+'+'+'(CASE ISNULL(convert(VARCHAR(8000),'+@column+',3),%d) WHEN %d THEN %s ELSE '+''''''''''+'+REPLACE(convert(VARCHAR(8000),'+@column+'),'''''''','''''''''''')+'+''''''''''+' END)' 
 
   END  
  END  
  SELECT @i=@i+1 
  Fetch CurColumns INTO @column,@xtype 
  END 
 CLOSE Curcolumns 
 DEALLOCATE Curcolumns 
 
 SELECT @exestr='SELECT convert(text,('+''''+'INSERT Into '+@TABLEname+' ('+@insertHead+') values( '+''''+'+'+@Values+'+'+''''+')'+''''+')) AS insertsql FROM '+@TABLEname+' WITH(NOLOCK) '+@Where
 SELECT @exestr=REPLACE(@exestr,'%s',''''+'NULL'+'''') 
 SELECT @exestr=REPLACE(@exestr,'%d',''''+'''') 
 EXEC (@exestr) 


GO




CREATE  PROCEDURE [dbo].[Usp_CreateDictionary]
AS
SET NOCOUNT ON
SELECT o.name AS DIC_TABLECode, c.name AS DIC_Code, s.name AS DIC_DataKind ,1 AS DIC_Display,c.length*6 AS Dic_Width ,c.colid AS Dic_IndexNo
 INTO #tmpDic FROM sysobjects o ,syscolumns c ,systypes s
 WHERE o.id=c.id AND c.xtype=s.xusertype AND o.xtype='u'
 AND o.name<>'dtproperties'
 ORDER BY o.name,c.colid

SET NOCOUNT off

--从系统表引入字段
INSERT INTO SM_Dictionary
(DIC_TABLECode,DIC_Code,DIC_DataKind,DIC_Display,Dic_Width,Dic_IndexNo)
SELECT DIC_TABLECode,DIC_Code,DIC_DataKind,DIC_Display,Dic_Width,Dic_IndexNo
 FROM #tmpDic a
 WHERE NOT EXISTS (SELECT dic_code FROM sm_dictionary WITH(NOLOCK) 
			WHERE dic_TABLEcode=a.DIC_TABLECode AND dic_code=a.DIC_Code )

UPDATE SM_Dictionary SET DIC_DataKind=a.DIC_DataKind,Dic_Width=a.Dic_Width,Dic_IndexNo=a.Dic_IndexNo
 FROM #tmpDic a
WHERE (SM_Dictionary.DIC_DataKind<>a.DIC_DataKind or SM_Dictionary.Dic_Width<>a.Dic_Width or SM_Dictionary.Dic_IndexNo<>a.Dic_IndexNo)
	AND (SM_Dictionary.dic_TABLEcode=a.DIC_TABLECode AND SM_Dictionary.dic_code=a.DIC_Code)

--引入表
INSERT INTO sm_Dictionary (dic_code)
SELECT name FROM sys.sysobjects 
 WHERE xtype='u'
 AND name NOT IN (SELECT dic_code FROM sm_dictionary WITH(NOLOCK))
 AND name<>'dtproperties'
 ORDER BY name

INSERT INTO SM_TABLEType (Tab_name,Tab_des,Tab_type)
SELECT Tab_name = name , Tab_des = name ,0 FROM sys.sysobjects 
 WHERE xtype='u'
 AND name NOT IN (SELECT Tab_name FROM SM_TABLEType WITH(NOLOCK))
 AND name <>'dtproperties'
 ORDER BY name

DELETE SM_TABLEType 
WHERE NOT EXISTS(SELECT name FROM sys.sysobjects 
		WHERE xtype='u' AND name<>'dtproperties' AND name=Tab_name)

DELETE sm_dictionary 
	WHERE NOT EXISTS(SELECT name FROM sys.sysobjects a WHERE xtype='u' AND dic_code=a.name) 
	AND NOT EXISTS(SELECT dic_code FROM #tmpDic WHERE #tmpDic.dic_code=sm_dictionary.dic_code)

GO


create procedure [dbo].[usp_dictionary](@tablename varchar(255))
as
begin
--添加字段注释  
--execute sp_addextendedproperty 'MS_Description','注释-些表为测试表','user','dbo','table','testTable','column','testRecord';  

--删除字段注释  
--execute sp_dropextendedproperty 'MS_Description','user','dbo','table','testTable','column','testRecord';  


--添加表注释  
--execute sp_addextendedproperty 'MS_Description','注释-些表为测试表','user','dbo','table','testTable',null,null;  


--修改表注释  
--execute sp_updateextendedproperty 'MS_Description','修改注释-测试表','user','dbo','table','testTable',null,null;  
  

--删除表注释  
--execute sp_dropextendedproperty 'MS_Description','user','dbo','table','testTable',null,null;  



select
[表名]=c.Name,
[表说明]=isnull(f.[value],''),
[列名]=a.Name,
[列序号]=a.Column_id,
[标识]=case when is_identity=1 then '√' else '' end,
[主键]=case when exists(select 1 from sys.objects where parent_object_id=a.object_id and type=N'PK' and name in
(select Name from sys.indexes where index_id in
(select indid from sysindexkeys where  colid=a.column_id)))
then '√' else '' end,
[类型]=b.Name,
[字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'
when b.Name='xml' then ' 2^31-1字节/2G'
else rtrim(a.[max_length]) end,
[长度]=ColumnProperty(a.object_id,a.Name,'Precision'),
[小数]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),
[是否為空]=case when a.is_nullable=1 then '√' else '' end,
[列说明]=isnull(e.[value],''),
[默认值]=isnull(d.text,'')
from
sys.columns a
left join
sys.types b on a.user_type_id=b.user_type_id
inner join
sys.objects c on a.object_id=c.object_id and c.Type='U'
left join
syscomments d on a.default_object_id=d.ID
left join
sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1
left join
sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1
--表名字
where c.Name = @tablename


/*
 
SELECT
(case when a.colorder=1 then d.name else '' end) [数据表名称],
a.colorder [列顺序],
a.name [列名称],
b.name as [列类型],
b.length as [列长度],
g.[value] AS [列说明信息]
FROM syscolumns a 
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id
--WHERE d.[name] ='table'--数据表名称
order by a.id,a.colorder



*/
end
GO



-- ----------------------------
-- Procedure structure for Usp_CreateNo
-- ----------------------------

CREATE PROCEDURE [dbo].[Usp_CreateNo](@V_ModuleName NVARCHAR(50),	@V_ParCode NVARCHAR(10) , @V_NewCode VARCHAR(50) OUTPUT)
as
begin
DECLARE @V_MDC_TABLEName VARCHAR(50), -- --表名
	@V_MDC_KeyField VARCHAR(50), -- 编号字段
	@V_MDC_KeyNo VARCHAR(50), -- 过去最后一个编号
	@V_MDC_NumberRule VARCHAR(50), -- 级别规则(项目编号规则)
	@V_MDC_NumberHead VARCHAR(20), -- 编码头
	@V_MDC_Type INT,-- 编号类别,0以编号规则来编号,1以编号级别来编号
	@V_MDC_Year INT,
	@V_MDC_Month INT,
	@V_MDC_Day INT,
	@V_MDC_Inc INT,
	@V_TotalLen int,
	@V_year VARCHAR(4),
	@V_month varchar(2),
	@V_day VARCHAR(2);

	IF EXISTS ( SELECT 1 FROM SM_ModuleCotrol WHERE MOD_Code = @V_ModuleName ) 
		SELECT   @V_MDC_TABLEName=ISNULL( MDC_TABLEName, '' ),	@V_MDC_KeyField=ISNULL( MDC_KeyField, '' ),	
		@V_MDC_KeyNo=ISNULL( MDC_KeyNo, '' ),	@V_MDC_NumberHead=ISNULL( MDC_NumberHead, '' ),	
		@V_MDC_NumberRule=ISNULL( MDC_NumberRule, 0 ),@V_MDC_Year=ISNULL( MDC_Year, 4 ),@V_MDC_Month=ISNULL( MDC_Month, 2 ),@V_MDC_Day=ISNULL( MDC_Day, 2 ), @V_MDC_Inc=ISNULL( MDC_Inc, 4 ) 			
		FROM
			SM_ModuleCotrol 
		WHERE
			 MOD_Code = @V_ModuleName ;		
	
	
 set @V_year=year(getdate());
 if (@V_MDC_Year)=2 	 
	 SET @V_year = right(year(getdate()),2);

 set @V_month=right('00'+MONTH(getdate()), 2);
 set @V_day=right('00'+Day(getdate()), 2);

 SET @V_TotalLen = len(@V_MDC_NumberHead) + @V_MDC_Year + @V_MDC_Month + @V_MDC_Day + @V_MDC_Inc;
 
		
		declare @V_GetNoSql varchar(8000),@V_MaxNo varchar(30)

	SET @V_GetNoSql=CONCAT('  SELECT @V_MaxNo=ISNULL(MAX(' ,@V_MDC_KeyField,'),'''')   FROM ' , @V_MDC_TABLEName,'  WHERE substring(',@V_MDC_KeyField,',1,len(''',@V_MDC_NumberHead,'''))=''',@V_MDC_NumberHead,''' AND substring(',@V_MDC_KeyField,',len(''',@V_MDC_NumberHead,''')+1,', @V_MDC_Year+ @V_MDC_Month+ @V_MDC_Day ,')=''',CONCAT(@V_year,@V_month,@V_day) ,'''
 AND len(',@V_MDC_KeyField,')=' , @V_TotalLen ,'''');		 
	
 
  EXECUTE @V_GetNoSql
	
  if ISNULL(@V_Maxno,'')='' 
	 set @V_MaxNO=CONCAT(@V_MDC_NumberHead,@V_year,@V_Month,@V_day,right(POWER(10,@V_MDC_Inc)+1,@V_MDC_Inc)) 
  else
	 set @V_MaxNO= CONCAT(@V_MDC_NumberHead,@V_year,@V_Month,@V_day ,right(CONCAT(POWER(10,@V_MDC_Inc), right(@V_MaxNo,@V_MDC_Inc )+1),@V_MDC_Inc))

	set @V_NewCode=@V_maxno;
	
  UPDATE SM_ModuleCotrol SET MDC_KeyNo=@V_NewCode WHERE  MOD_Code=@V_ModuleName;
	
end

GO




--写放所有表
insert into SM_TableType(tab_name,tab_des,tab_class) select name,name,0 from sys.objects where  type='U'
go